package org.shj.tdpl.loader.loader.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.shj.tdpl.common.constant.TdplConstants;
import org.shj.tdpl.common.utils.StringUtil;
import org.shj.tdpl.loader.loader.DataLoader;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicBoolean;

/**
 *
 * @author ShenHuangJian
 * @date 2018年6月28日
 */
@Slf4j
public abstract class AbstractXlsxDataLoader implements DataLoader {

	protected String generatedFilePath;

	protected JdbcTemplate jdbcTemplate;

	/**
	 * key -- data version; value -- table names
	 */
	private static Map<String, String> versionTableMap;

	/**
	 * key -- table name; value -- the column index of delete sql
	 */
	private Map<String, Integer> colNumMap;

	/**
	 * the parent table is before child table. contains all tables.
	 */
	private String[] sortedTableArr;
	
	private static Map<String, List<String>> threadTablesMap = new ConcurrentHashMap<>();
	
	private static AtomicBoolean initialized = new AtomicBoolean(false);

	@Override
	public void loadDataByVersion(int... versions) {
		try {
			if(!initialized.get()){
				synchronized(initialized){
					init();
				}
			}
			List<String> tables = getSortedTablesForLoadData(versions);
			waitUntillTheTablesAreOkToDelete(tables);
			log.debug("Thread: " + Thread.currentThread().getName() + " is beginning to load data.");
			loadData(tables, versions);
		}catch(Exception e) {
			log.error("Load test data failed.", e);
		}
	}
	
	private void waitUntillTheTablesAreOkToDelete(List<String> tables){
		synchronized(threadTablesMap){
			while(hasThreadProcessSomeSameTable(tables)){
				try{
					log.debug("Thread: " + Thread.currentThread().getName() + " wait().");
					threadTablesMap.wait();
				}catch(InterruptedException e){
					log.error(e.getMessage(), e);
				}
			}
		}
	}
	
	@Override
	public void clean(){
		synchronized(threadTablesMap){
			String name = Thread.currentThread().getName();
			threadTablesMap.remove(name);
			threadTablesMap.notifyAll();
			log.debug("Thread: " + Thread.currentThread().getName() + " finished.");
		}
	}

	/**
	 * 加载数据
	 * @param tables 哪些表要加载数据
	 * @param versions 加载哪些版本的数据
	 * @throws Exception
	 */
	protected abstract void loadData(List<String> tables, int... versions) throws Exception;

	/**
	 * 获得所有表列表的那个 workbook
	 * @return
	 * @throws Exception
	 */
	protected abstract XSSFWorkbook getCommonInfoBook() throws Exception;
		
	protected List<String> getSortedTablesForLoadData(int... versions){
		//store all the tables maps to the versions
		Set<String> allTables = new HashSet<>();
		
		String tables;
		for(int version : versions){
			tables = versionTableMap.get(version+"");
			if(StringUtil.isEmpty(tables)){
				throw new RuntimeException("请在 Version Table Map 这个 sheet 中配置测试数据对应的表。");
			}
			allTables.addAll(Arrays.asList(tables.split(TdplConstants.SEPARATE)));
		}
		
		List<String> sorted = new ArrayList<>();
		for(String tbl : sortedTableArr){
			boolean isNotExist = allTables.add(tbl);
			if(!isNotExist){
				sorted.add(tbl);
			}
		}
		
		return sorted;
	}
	
	/**
	 * Current thread may delete the data in one table, but it is still need by another thread.
	 * So we need to hang on this thread and wait for another
	 * @param tables
	 * @return
	 */
	private boolean hasThreadProcessSomeSameTable(List<String> tables){
		List<String> tablesInUse = null;
		boolean flag = false;
		for(String thread : threadTablesMap.keySet()){
			tablesInUse = threadTablesMap.get(thread);
			for(String tbl : tables){
				for(String inTbl : tablesInUse){
					if(tbl.equalsIgnoreCase(inTbl)){
						flag = true;
						break;
					}
				}
				if(flag) {
					break;
				}
			}
			if(flag) {
				break;
			}
		}

		if(!flag){
			threadTablesMap.put(Thread.currentThread().getName(), tables);
		}
		return flag;
	}
	
	protected void getSqlsNeedToBeExecute(XSSFSheet sheet, FormulaEvaluator evaluator,List<String> insert, List<String> delete, 
			List<String> updateAfterInsert, List<String> updateBeforeDel,  int... versions){
		
		String tblNm = sheet.getSheetName();
		//some table maybe haven't the version data
		List<String> loadVersions = getVersionsNeedToBeLoadByTable(tblNm, versions);
		int delSqlColIndex = colNumMap.get(tblNm);
		
		//1. get delete sql
		delete.add("Delete From " + tblNm);
		
		//2. get updateBeforeDelete sql. In some case, tableA has FK in tableB and tableB has FK in tableA, we can't directly delete
		//the record, we need to update the fk column to NULL in one table. 
		//E.g. Department has EmployeeId(department management) fk. And Employee has DeptId fk.
		XSSFRow row = sheet.getRow(TdplConstants.UPDATE_BEFORE_DEL_ROW_IND);
		
		int updateBeforeDelColInd = delSqlColIndex;
		String sql;
		XSSFCell cell;

		while(true){
			cell = row.getCell(updateBeforeDelColInd);
			if(cell != null){
				sql = cell.getStringCellValue();
				if(StringUtil.isNotEmpty(sql)){
					updateBeforeDel.add(sql);
					
				}else{
					break;
				}
			}else{
				break;
			}
			updateBeforeDelColInd++;
		}
		
		//3. get insert and updateAfterInsertMap sql
		// the Row 1 ~ 3 are header
		int rowNum = TdplConstants.FIRST_CONTENT_ROW_IND;
		for(; rowNum <= sheet.getLastRowNum(); rowNum++){
			row = sheet.getRow(rowNum);
			if(row == null){
				// the row is empty
				continue;
			}
			
			cell = row.getCell(0);
			if(cell == null){
				//there is no version in this row, it's dirty data.
				continue;
			}
			
			String version = cell.getStringCellValue();			
			boolean loadCurrentDataLine = false;
			
			if(StringUtil.isNotEmpty(version) && loadVersions.contains(version)){
				loadCurrentDataLine = true;
			}
			
			if(loadCurrentDataLine){
				addSqlToList(row, delSqlColIndex + 4, evaluator, insert);
				addSqlToList(row, delSqlColIndex + 2, evaluator, updateAfterInsert);
				
			}
		}
		
	}
	
	private void addSqlToList(XSSFRow row, int index, FormulaEvaluator evaluator, List<String> list){
		XSSFCell cell = row.getCell(index);
		if(cell != null){
			//some tables havn't primary key
			String sql = evaluator.evaluate(cell).getStringValue();
			if(StringUtil.isNotEmpty(sql)){
				list.add(evaluator.evaluate(cell).getStringValue());
			}
		}
	}
	
	private List<String> getVersionsNeedToBeLoadByTable(String tableName, int... versions){
		String[] tables;
		List<String> loadVersions = new ArrayList<String>();
		for(int v : versions){
			tables = versionTableMap.get(v+"").split(TdplConstants.SEPARATE);
			for(String tbl : tables){
				if(tbl.trim().equalsIgnoreCase(tableName)){
					loadVersions.add(v + "");
					break;
				}
			}
		}
		return loadVersions;
	}
		
	/**
	 * init the versionTableMap, it will load all the data in VersionTableMap sheet. 
	 */
	public void init() throws Exception{
		if(initialized.get()){
			return;
		}
		versionTableMap = new HashMap<>(16);
		XSSFWorkbook book = getCommonInfoBook();
		
		initVersionTableMap(book);
		loadInfoInIndexSheet(book);
		initialized.set(true);
	}
	
	private void loadInfoInIndexSheet(XSSFWorkbook book){
		log.debug("get column number of each table");
		
		colNumMap = new HashMap<String,Integer>();
		
		XSSFSheet indSheet = book.getSheet(TdplConstants.INDEX_SHEET_NAME);
		XSSFRow row = null;
		String tblNm = null;

		//the first row is header, so begin 1
		for(int i = 1 ; i <= indSheet.getLastRowNum(); i++){
			row = indSheet.getRow(i);
			if(row.getCell(0) != null && row.getCell(1) != null){
				tblNm = row.getCell(0).getStringCellValue();
				colNumMap.put(tblNm, (int)row.getCell(1).getNumericCellValue());
			}
		}

		// parent table is in the front of child table
		String sortedTables = indSheet.getRow(0).getCell(TdplConstants.SORTED_TABLE_COL_IND + 1).getStringCellValue();
		sortedTableArr = sortedTables.split(TdplConstants.SEPARATE);
	}
	
	private void initVersionTableMap(XSSFWorkbook book){
		XSSFSheet sheet = book.getSheet(TdplConstants.VERION_TABLE_MAP_SHEET_NAME);
		XSSFRow row = null;
		XSSFCell cell = null;
		Integer version = 0;
		String tables = null;
		
		if(sheet == null){
			log.warn("The \"" + TdplConstants.VERION_TABLE_MAP_SHEET_NAME + "\" doesn't exist. Please re-generate the schema excel.");
			return;
		}
		for(int i = 1 ; i <= sheet.getLastRowNum(); i++){
			row = sheet.getRow(i);
			cell = row.getCell(0);
			version = cell == null ? null : (int)cell.getNumericCellValue();
			
			cell = row.getCell(1);
			tables = cell == null ? "" : cell.getStringCellValue();
			
			if(version != null){
				if("".equals(tables.trim())){
					log.warn("The table names for data version(" + version + ") doesn't been specified.");
				}else{
					versionTableMap.put(version + "", tables);
				}				
			}
		}
	}
	
	protected void executeSqls(List<String> insert, List<String> delete, 
			List<String> updateAfterInsert, List<String> updateBeforeDel){
		executeSqls(updateBeforeDel, false);
		executeSqls(delete, true);
		executeSqls(insert, false);
		executeSqls(updateAfterInsert, false);
	}

	private void executeSqls(List<String> sqls, boolean reverse){
		if(sqls != null && !sqls.isEmpty() ){
			if(reverse){
				for(int i = sqls.size() - 1 ; i >= 0 ; i--){
					executeSql(sqls.get(i));
				}
			}else{
				for(String sql : sqls){				
					executeSql(sql);
				}
			}
		}
	}
	
	private void executeSql(String sql){
		log.info(sql);
		if(sql.lastIndexOf(TdplConstants.SEPARATE) == sql.length() - 1){
			sql = sql.substring(0,sql.length()-1);
		}
		jdbcTemplate.execute(sql);
	}

	

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public String getGeneratedFilePath() {
		return generatedFilePath;
	}

	public void setGeneratedFilePath(String generatedFilePath) {
		this.generatedFilePath = generatedFilePath;
	}
	
	
}
